package org.price.sql.repo.session.db.dialect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.log4j.Logger;
import org.price.sql.repo.document.model.PropertyInfo;
import org.price.sql.repo.documents.config.SqlType;

public class DialectH2 extends DialectAbstract {

    private static final Logger LOG = Logger.getLogger(DialectH2.class);

    public DialectH2(Connection connection) throws SQLException {
        super(connection);
    }

    @Override
    public String getNormalizedName(String originalName, int length)
            throws SQLException {
        return super.getNormalizedName(originalName, 9999);
    }

    @Override
    public String getSqlStringFromSqlType(String tableName,
            PropertyInfo property) throws SQLException {

        if (property.getDocProperty().getType() == SqlType.BOOLEAN) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " BOOLEAN "
                            : " BOOLEAN NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.TINYINT) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " \"char\" "
                            : " \"char\" NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.SMALLINT) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " SMALLINT "
                            : " SMALLINT NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.INTEGER) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " INTEGER "
                            : " INTEGER NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.LONG) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " BIGINT "
                            : " BIGINT NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.DOUBLE) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " FLOAT "
                            : " FLOAT NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.FLOAT) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " REAL "
                            : " REAL NOT NULL ");
            return sqlStr;
        }

        else if (property.getDocProperty().getType() == SqlType.BLOB) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " OID "
                            : " OID NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.CBLOB) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " TEXT "
                            : " TEXT NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.TIMESTAMP) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " TIMESTAMPTZ "
                            : " TIMESTAMPTZ NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.DATE) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " DATE "
                            : " DATE NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.TIME) {

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    property.getDocProperty().isNullable() ? " TIME "
                            : " TIME NOT NULL ");
            return sqlStr;

        }

        else if (property.getDocProperty().getType() == SqlType.VARCHAR) {
            StringBuilder varchar = new StringBuilder(" VARCHAR(");
            varchar.append(property.getDocProperty().getLength() == null ? 255
                    : property.getDocProperty().getLength());
            varchar.append(')');
            varchar.append(property.getDocProperty().isNullable() ? ""
                    : " NOT NULL");

            String sqlStr = String.format(
                    createColumnString,
                    getNormalizedTableName(tableName),
                    getNormalizedColumnName(property.getDocProperty().getName()),
                    varchar.toString());
            return sqlStr;

        }

        return null;
    }

    @Override
    protected void buildIndexCreateString() {
        StringBuilder ctSb = new StringBuilder("CREATE INDEX ");
        ctSb.append(quoteString);
        ctSb.append("%s");
        ctSb.append(quoteString);
        ctSb.append(" ON ");
        ctSb.append(quoteString);
        ctSb.append("%s");
        ctSb.append(quoteString);
        ctSb.append("(%s)");

        createIndexString = ctSb.toString();
    }

    @Override
    public void buildUniqueCreateString() {
        StringBuilder ctSb = new StringBuilder("CREATE UNIQUE INDEX ");
        ctSb.append(quoteString);
        ctSb.append("%s");
        ctSb.append(quoteString);
        ctSb.append(" ON ");
        ctSb.append(quoteString);
        ctSb.append("%s");
        ctSb.append(quoteString);
        ctSb.append("(%s)");

        createUniqueString = ctSb.toString();
    }

    @Override
    public int createIndex(String name, String tableName, String type,
            String... propNames) throws SQLException {
        if (createIndexString == null) {
            buildIndexCreateString();
        }

        StringBuilder sb = new StringBuilder();
        for (String prop : propNames) {
            sb.append(quoteString);
            sb.append(prop);
            sb.append(quoteString);
            sb.append(',');
        }

        sb.delete(sb.length() - 1, sb.length());

        LOG.info("Creating index " + name);
        String uStr = String.format(createIndexString,
                getNormalizedConstraintName(name),
                getNormalizedTableName(tableName), sb.toString());

        PreparedStatement pStm = null;
        pStm = getConnection().prepareStatement(uStr);
        int ret = pStm.executeUpdate();
        pStm.close();

        return ret;
    }

    @Override
    public int createUnique(String name, String tableName, String type,
            String... propNames) throws SQLException {
        if (createUniqueString == null) {
            buildUniqueCreateString();
        }

        StringBuilder sb = new StringBuilder();
        for (String prop : propNames) {
            sb.append(quoteString);
            sb.append(prop);
            sb.append(quoteString);
            sb.append(',');
        }

        sb.delete(sb.length() - 1, sb.length());

        LOG.info("Creating unique constraints " + name);
        String uStr = String.format(createUniqueString,
                getNormalizedConstraintName(name),
                getNormalizedTableName(tableName), sb.toString());

        PreparedStatement pStm = null;
        pStm = getConnection().prepareStatement(uStr);
        int ret = pStm.executeUpdate();
        pStm.close();

        return ret;
    }

}
